* clean mcvl raw data
* store data in separate tables
**** OUTLINE ****
* prelims
* table 1 - personal
* table 2 - histories
* table 3 - contributions
* table 4 - pensions
* table 5 - cohabitants
* table 6 - fiscal
* closing
********************************************************************************
* prelims
********************************************************************************
{
clear all
cap log close
set more off
}
********************************************************************************
* table 1 - personal
********************************************************************************
{

** MCVL 2017-2009 
forval year = 2017(-1)2009 {
import delimited "$orig/MCVL-`year' CDF/pers/MCVL`year'PERSONAL_CDF.TXT", delimiter(";") clear
duplicates drop v1, force
rename v1 id
rename v2 birth`year'
rename v4 nat`year'
rename v9 country`year'
rename v7 residence`year'
rename v10 educ`year'
sort id
compress
save "$data/temp/mcvl`year'_01pers.dta", replace
}

** MCVL 2008-2005
forval year = 2008(-1)2005 {
import delimited "$orig/MCVL-`year' CDF/pers/PERSANON.trs", delimiter(";") clear
duplicates drop v1, force
rename v1 id
rename v2 birth`year'
rename v4 nat`year'
rename v9 country`year'
rename v7 residence`year'
rename v10 educ`year'
sort id
compress
save "$data/temp/mcvl`year'_01pers.dta", replace
}

** MCVL 2004
forval year = 2004(-1)2004 {
import delimited "$orig/MCVL-`year' CDF/pers/PERSANON.txt", delimiter(whitespace) clear
duplicates drop v1, force
replace v10 = v8 if v5 != .
replace v9 = v7 if v5 != .
replace v8 = v6 if v5 != .
rename v1 id
rename v2 birth`year'
rename v4 nat`year'
drop v5 v6 v7
rename v8 v5
rename v9 residence`year'
rename v10 v8
sort id
compress
save "$data/temp/mcvl`year'_01pers.dta", replace
}

** finish cleaning each year
	* generate labels 
	{
	* gender
label define gender 1 "male" 2 "female"
	* education
#d;
label define education 
	0 "<16 y.o." 
	10 "illiterate" 
	11 "illiterate"
	20 "less than secondary school" 
	21 "no studies" 
	22 "incomplete secondary school"
	30 "secondary school or equivalent" 
	31 "secondary school or equivalent" 
	32 "secondary school or equivalent"
	40 "high school or more" 
	41 "professional degree" 
	42 "high school" 
	43 "other intermediate degrees"
	44 "college" 
	45 "architect/engineer" 
	46 "university degree" 
	47 "non-university higher degrees"
	48 "master degree or more" 
	99 "unknown";
#d cr
	* province
#d;
label define province 
	1 "Alaba" 
	2 "Albacete" 
	3 "Alicante" 
	4 "Almeria" 
	5 "Avila"
	6 "Badajoz" 
	7 "Baleares" 
	8 "Barcelona" 
	9 "Burgos" 
	10 "Caceres" 
	11 "Cadiz"
	12 "Castellon" 
	13 "Ciudad Real" 
	14 "Cordoba" 
	15 "Coruña" 
	16 "Cuenca"
	17 "Girona" 
	18 "Granada" 
	19 "Guadalajara" 
	20 "Gipuzkoa" 
	21 "Huelva" 
	22 "Huesca" 
	23 "Jaen" 
	24 "Leon" 
	25 "Lleida" 
	26 "Rioja" 
	27 "Lugo" 
	28 "Madrid"
	29 "Malaga" 
	30 "Murcia" 
	31 "Navarra" 
	32 "Ourense" 
	33 "Asturias" 
	34 "Palencia"
	35 "Palmas" 
	36 "Pontevedra" 
	37 "Salamanca" 
	38 "Santa Cruz de Tenerife"
	39 "Cantabria" 
	40 "Segovia" 
	41 "Sevilla" 
	42 "Soria" 
	43 "Tarragona" 
	44 "Teruel"
	45 "Toledo" 
	46 "Valencia" 
	47 "Valladolid" 
	48 "Bizkaia" 
	49 "Zamora" 
	50 "Zaragoza"
	51 "Ceuta" 
	52 "Melilla" 
	53 "Ferrol" 
	56 "Vilagarcia de Arousa" 
	66 "Abroad";
#d cr
	}
* rename variables
forval year=2017(-1)2004 {

* load data
use "$data/temp/mcvl`year'_01pers.dta", clear  

* rename and label
label var id "1.001 person id"
rename v3 gender
label var gender "1.003 gender"
label val gender gender

	if `year' != 2004 {
foreach var in nat country { 

replace `var'`year'="Spain" if `var'`year'=="N00"
replace `var'`year'="Germany" if `var'`year'=="N01"
replace `var'`year'="Argentina" if `var'`year'=="N03"
replace `var'`year'="Bulgaria" if `var'`year'=="N04"
replace `var'`year'="China" if `var'`year'=="N05"
replace `var'`year'="Colombia" if `var'`year'=="N06"
replace `var'`year'="Cuba" if `var'`year'=="N07"
replace `var'`year'="Dominican Republic" if `var'`year'=="N08"
replace `var'`year'="Ecuador" if `var'`year'=="N09"
replace `var'`year'="France" if `var'`year'=="N10"
replace `var'`year'="Italy" if `var'`year'=="N11"
replace `var'`year'="Morocco" if `var'`year'=="N12"
replace `var'`year'="Peru" if `var'`year'=="N13"
replace `var'`year'="Poland" if `var'`year'=="N14"
replace `var'`year'="Portugal" if `var'`year'=="N15"
replace `var'`year'="Great Britain" if `var'`year'=="N16"
replace `var'`year'="Romania" if `var'`year'=="N17"
replace `var'`year'="Ukraine" if `var'`year'=="N18"
replace `var'`year'="Rest of EU-15" if `var'`year'=="N19"
replace `var'`year'="Rest of UE-Expanded" if `var'`year'=="N20"
replace `var'`year'="Rest of European countries" if `var'`year'=="N21"
replace `var'`year'="Rest of South and Central America" if `var'`year'=="N22"
replace `var'`year'="Rest of African countries" if `var'`year'=="N23"
replace `var'`year'="Rest of Asia-Pacific country" if `var'`year'=="N24"
replace `var'`year'="Other countries" if `var'`year'=="N25"
replace `var'`year'="Bolivia" if `var'`year'=="N26"
replace `var'`year'="Brazil" if `var'`year'=="N27"
replace `var'`year'="NA" if `var'`year'=="N99"
} 
* label
label var nat`year' "1.004 nationality in `year'"
label var country`year' "1.009 country of origin in mcvl `year'"
	}
rename v5 province
label var province "1.005 province of birth"
label val province province

if `year' != 2004 {
	rename v6 profirst
	label var profirst "1.006 province of first affiliation"
	label val profirst province
}

rename v8 death
label var death "1.008 death date"
if `year' != 2004 {
	label var educ`year' "1.010 education in `year'"
	destring educ`year', replace force
	label val educ`year' education
}

cap drop v11

* store
sort id
format id %9.0g
compress
save "$data/mcvl`year'_01pers.dta", replace
cap erase "$data/temp/mcvl`year'_01pers.dta"
}


** put together
* 2004-16
use "$data/mcvl2016_01pers.dta", clear
forval year = 2015(-1)2004 {
merge 1:1 id using "$data/mcvl`year'_01pers.dta", force
drop _merge
}
tostring birth2016, replace
replace birth2016="" if birth2016=="."
forval year = 2015(-1)2005 {
local yearplus1 = `year'+1
g x =(regexm(country`yearplus1',"N.")==1 & (regexm(country`year', " .")==1|country`year'==""))
replace country`year' = country`yearplus1' if x==1
g y =(regexm(nat`yearplus1',"N00")==1 & (regexm(nat`year', " .")==1|nat`year'==""))
replace nat`year' = nat`yearplus1' if y==1
g z =((regexm(birth`yearplus1',"1.")==1|regexm(birth`yearplus1',"2.")==1) & (regexm(birth`year', " .")==1|birth`year'==""))
replace birth`year' = birth`yearplus1' if z==1
drop x y z
}
forval year = 2005(1)2015 {
local yearplus1 = `year'+1
g x =(regexm(country`year',"N.")==1 & (regexm(country`yearplus1', " .")==1|country`yearplus1'==""))
replace country`yearplus1' = country`year' if x==1
g y =(regexm(nat`year',"N00")==1 & (regexm(nat`yearplus1', " .")==1|nat`yearplus1'==""))
replace nat`yearplus1' = nat`year' if y==1
g z =((regexm(birth`year',"1.")==1|regexm(birth`year',"2.")==1) & (regexm(birth`yearplus1', " .")==1|birth`yearplus1'==""))
replace birth`yearplus1' = birth`year' if z==1
drop x y z
}
* store data
sort id
format id %9.0g
compress
save "$data/mcvl2004-16_01pers.dta", replace
* ids
use "$data/mcvl2004-16_01pers.dta", clear
keep id
g mcvl=.
forval year=2016(-1)2004 {
merge 1:1 id using "$data/mcvl`year'_01pers.dta", keepusing(id)
g mcvl`year'=(_merge==3)
replace mcvl=`year' if (mcvl`year'==1 & mcvl==.)
drop _merge
}
save "$data/temp/mcvl2004-2016_all_ids.dta", replace

* 2004-17
use "$data/mcvl2017_01pers.dta", clear
forval year = 2016(-1)2004 {
merge 1:1 id using "$data/mcvl`year'_01pers.dta", force
drop _merge
}
tostring birth2017, replace
replace birth2017="" if birth2017=="."
tostring birth2016, replace
replace birth2016="" if birth2016=="."
forval year = 2016(-1)2005 {
local yearplus1 = `year'+1
g x =(regexm(country`yearplus1',"N.")==1 & (regexm(country`year', " .")==1|country`year'==""))
replace country`year' = country`yearplus1' if x==1
g y =(regexm(nat`yearplus1',"N00")==1 & (regexm(nat`year', " .")==1|nat`year'==""))
replace nat`year' = nat`yearplus1' if y==1
g z =((regexm(birth`yearplus1',"1.")==1|regexm(birth`yearplus1',"2.")==1) & (regexm(birth`year', " .")==1|birth`year'==""))
replace birth`year' = birth`yearplus1' if z==1
drop x y z
}
forval year = 2005(1)2016 {
local yearplus1 = `year'+1
g x =(regexm(country`year',"N.")==1 & (regexm(country`yearplus1', " .")==1|country`yearplus1'==""))
replace country`yearplus1' = country`year' if x==1
g y =(regexm(nat`year',"N00")==1 & (regexm(nat`yearplus1', " .")==1|nat`yearplus1'==""))
replace nat`yearplus1' = nat`year' if y==1
g z =((regexm(birth`year',"1.")==1|regexm(birth`year',"2.")==1) & (regexm(birth`yearplus1', " .")==1|birth`yearplus1'==""))
replace birth`yearplus1' = birth`year' if z==1
drop x y z
}
* store
sort id
format id %9.0g
compress
save "$data/mcvl2004-17_01pers.dta", replace
* ids
use "$data/mcvl2004-17_01pers.dta", clear
keep id
g mcvl=.
forval year=2017(-1)2004 {
merge 1:1 id using "$data/mcvl`year'_01pers.dta", keepusing(id)
g mcvl`year'=(_merge==3)
replace mcvl=`year' if (mcvl`year'==1 & mcvl==.)
drop _merge
}
save "$data/temp/mcvl2004-2017_all_ids.dta", replace

forval year=2017(-1)2004 {
cap erase "$data/mcvl`year'_01pers.dta"
}

}
********************************************************************************
* table 2 - histories
********************************************************************************
{
** MCVL 2017
* load affiliation data
foreach num of numlist 1/4 {
clear all
insheet using "$orig/MCVL-2017 CDF/affi/MCVL2017AFILIAD`num'_CDF.TXT", delimiter(";") 
* store data
compress
save "$data/temp/mcvl2017_02affi_`num'", replace
}
* append
use "$data/temp/mcvl2017_02affi_1", clear
foreach num of numlist 2/4 {
append using "$data/temp/mcvl2017_02affi_`num'"
}
* store joined dataset
sort v1 v6 v7
compress
save "$data/temp/mcvl2017_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/4 {
cap erase "$data/temp/mcvl2017_02affi_`num'.dta"
}

** MCVL 2016
* load affiliation data
foreach num of numlist 1/4 {
clear all
insheet using "$orig/MCVL-2016 CDF/affi/MCVL2016AFILIAD`num'_CDF.TXT", delimiter(";") 
* store data
compress
save "$data/temp/mcvl2016_02affi_`num'", replace
}
* append
use "$data/temp/mcvl2016_02affi_1", clear
foreach num of numlist 2/4 {
append using "$data/temp/mcvl2016_02affi_`num'"
}
* store joined dataset
sort v1 v6 v7
compress
save "$data/temp/mcvl2016_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/4 {
cap erase "$data/temp/mcvl2016_02affi_`num'.dta"
}

** MCVL 2015-2013
forval year = 2015(-1)2013 { 
* load affiliation data
foreach num of numlist 1/4 {
clear all
insheet using "$orig/MCVL-`year' CDF/affi/MCVL`year'AFILIAD`num'_CDF.TXT", delimiter(";") 
* store data
compress
save "$data/temp/mcvl`year'_02affi_`num'.dta", replace
}
* append
use "$data/temp/mcvl`year'_02affi_1.dta", clear
foreach num of numlist 2/4 {
append using "$data/temp/mcvl`year'_02affi_`num'.dta"
}
* store joined dataset
sort v1 v6 v7
compress
save "$data/temp/mcvl`year'_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/4 {
cap erase "$data/temp/mcvl`year'_02affi_`num'.dta"
}
} 

** MCVL 2012
forval year = 2012(-1)2012 { 
* load affiliation data
foreach num of numlist 1/3 {
clear all
insheet using "$orig/MCVL-`year' CDF/affi/MCVL2012AFILIAD`num'_CDF.TXT", delimiter(";") 
* store data
compress
save "$data/temp/mcvl`year'_02affi_`num'.dta", replace
}
* append datasets
use "$data/temp/mcvl`year'_02affi_1.dta", clear
foreach num of numlist 2/3 {
append using "$data/temp/mcvl`year'_02affi_`num'.dta"
}
* store joined dataset
sort v1 v6 v7
compress
save "$data/temp/mcvl`year'_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/3 {
cap erase "$data/temp/mcvl`year'_02affi_`num'.dta"
}
}

** MCVL 2011
* load affiliation data
foreach num of numlist 1/3 {
clear all
insheet using "$orig/MCVL-2011 CDF/affi/MCVL2011.F2013.AFILIA`num'_CDF.TXT", delimiter(";") 
* store data
compress
save "$data/temp/mcvl2011_02affi_`num'.dta", replace
}
* append datasets
use "$data/temp/mcvl2011_02affi_1.dta", clear
foreach num of numlist 2/3 {
append using "$data/temp/mcvl2011_02affi_`num'.dta"
}
* store joined dataset
sort v1 v6 v7
compress
save "$data/temp/mcvl2011_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/3 {
cap erase "$data/temp/mcvl2011_02affi_`num'.dta"
}

** MCVL 2010-2009
forval year = 2010(-1)2009 { 
* load affiliation data
foreach num of numlist 1/3 {
clear all
insheet using "$orig/MCVL-`year' CDF/affi/MCVL`year'AFILIAD`num'_CDF.TXT", delimiter(";") 
* store data
compress
save "$data/temp/mcvl`year'_02affi_`num'.dta", replace
}
* append
use "$data/temp/mcvl`year'_02affi_1.dta", clear
foreach num of numlist 2/3 {
append using "$data/temp/mcvl`year'_02affi_`num'.dta"
}
* store
sort v1 v6 v7
compress
save "$data/temp/mcvl`year'_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/3 {
cap erase "$data/temp/mcvl`year'_02affi_`num'.dta"
}
} 

** MCVL 2008-2005
forval year = 2008(-1)2005 { 
* load affiliation data
foreach num of numlist 1/3 {
clear all
insheet using "$orig/MCVL-`year' CDF/affi/AFILANON`num'.trs", delimiter(";") 
* store data
compress
save "$data/temp/mcvl`year'_02affi_`num'.dta", replace
}
* append
use "$data/temp/mcvl`year'_02affi_1.dta", clear
foreach num of numlist 2/3 {
append using "$data/temp/mcvl`year'_02affi_`num'.dta"
}
* store
sort v1 v6 v7
compress
save "$data/temp/mcvl`year'_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/3 {
cap erase "$data/temp/mcvl`year'_02affi_`num'.dta"
}
} 

** MCVL 2004
* load affiliation data
foreach num of numlist 1/3 {
import delimited "$orig/MCVL-2004 CDF/affi/AFILANON`num'.txt", delimiter(whitespace, collapse) encoding(UTF-8) clear
* store data
compress
save "$data/temp/mcvl2004_02affi_`num'.dta", replace
}
* append
use "$data/temp/mcvl2004_02affi_1", clear
foreach num of numlist 2/3 {
append using "$data/temp/mcvl2004_02affi_`num'.dta", force
}
* store
sort v1 v6 v7
compress
save "$data/temp/mcvl2004_02affi.dta", replace
* erase intermediate datasets
foreach num of numlist 1/3 {
cap erase "$data/temp/mcvl2004_02affi_`num'.dta"
}

** employee information 2017-04
forval year = 2017(-1)2004 {
use "$data/temp/mcvl`year'_02affi.dta", clear
keep v10 v11 v13
rename v10 ccc
rename v13 emplccc`year'
rename v11 ccc_mun`year'
duplicates drop ccc, force
sort ccc
save "$data/temp/mcvl`year'_02affi_emplccc.dta", replace
}

** put together
* 2004-16
use "$data/temp/mcvl2016_02affi_emplccc.dta", clear
forval year = 2015(-1)2004 {
merge 1:1 ccc using "$data/temp/mcvl`year'_02affi_emplccc.dta"
drop _m
}
sort ccc
save "$data/temp/mcvl2004-16_02affi_emplccc.dta", replace

* 2004-17
use "$data/temp/mcvl2017_02affi_emplccc.dta", clear
forval year = 2016(-1)2004 {
merge 1:1 ccc using "$data/temp/mcvl`year'_02affi_emplccc.dta"
drop _m
}
sort ccc
save "$data/temp/mcvl2004-17_02affi_emplccc.dta", replace

** clean
* predefine labels
	{
	* contribution regime
#d;
label define contregime 
	1 "general" 
	2 "self-employed" 
	3 "agrarian (employed)"
	4 "agrarian (self-employed)" 
	5 "sea" 
	6 "mining" 
	7 "home employees";
#d cr
	* contribution group
#d;
label define contgroup 
	0 "NA" 
	1 "Engineers, College grads & top management"
	2 "Technical engineers, experts and assistants" 
	3 "Administrative managers"
	4 "Assistants without a degree" 
	5 "Administrative officals" 
	6 "'Subalternos'"
	7 "Administrative assistants" 
	8 "First and second degree officials"
	9 "Third degree officials and experts" 
	10 "Non-qualified adults" 
	11 "Non-adult workers"
	12 "Workers younger than 17 y.o.";
#d cr
	* reasonend
#d;
label define reasonend2013 
	0 "NA" 
	51 "Voluntary" 
	52 "End of activity" 
	54 "Non-voluntary"
	55 "M&A" 
	56 "Death" 
	58 "Transition to pensionist" 
	60 "Revision, as per admin" 
	65 "IT" 
	68 "Maternity leave"
	69 "Temporary suspension (ERE)" 
	73 "Relatives' leave" 
	74 "End of contract"
	77 "Collective dismissal" 
	91 "Objective causes (firm)" 
	92 "Objective causes (worker)"
	93 "End of temporary contract" 
	94 "Transition to inactivity of discontinuos-fixed workers"
	99 "Other causes";
#d cr
	* reasonend 2007-2012
#d;
label define reasonend2007 
	0 "NA" 
	51 "Voluntary" 
	54 "Non-voluntary" 
	55 "M&A"
	56 "Death" 
	58 "Transition to pensionist" 
	60 "Revision" 
	61 "Military service" 
	64 "Strike"
	65 "End of transitory incapacity" 
	68 "Maternity leave" 
	69 "Temporary suspension (ERE)"
	73 "Relatives' leave" 
	74 "End of contract" 
	81 "Regime change"
	91 "Objective causes (firm)" 
	92 "Objective causes (worker)"
	93 "End of temporary contract" 
	94 "Transition to inactivity of discontinuos-fixed workers"
	99 "Other causes";
#d cr
	* reasonend
#d;
label define reasoend20072013 
	0 "NA" 
	51 "Voluntary" 
	52 "End of activity" 
	54 "Non-voluntary" 
	55 "M&A" 
	56 "Death" 
	58 "Transition to pensionist" 
	60 "Revision, as per admin" 
	61 "Military service" 
	64 "Strike" 
	65 "IT" 
	68 "Maternity leave" 
	69 "Temporary suspension (ERE)" 
	73 "Relatives' leave" 
	74 "End of contract" 
	77 "Collective dismissal" 
	81 "Regime change" 
	91 "Objective causes (firm)" 
	92 "Objective causes (worker)" 
	99 "Other causes";
#d cr
	* reltype
#d;
label define reltype 
	0 "NA (no specifities)" 
	87 "Apprenticeship/Formation contract"
	400 "Some specificity such that worker is not considered registered"
	500 "Some specificity that does not prevent considering the worker as registered"
	751 "UI (extinction of contract)" 
	752 "UI (suspension of contract)"
	753 ">52/55 y.o. subsidy (extinction of contract)"
	754 ">52/55 y.o. subsidy (suspension of contract)"
	755 "UI, including agriculture (extinction of contract)"
	756 "UI (suspension of contract)" 
	901 "Civil servants and 'personal estatutario'"
	902 "Interim civil servants" 
	910 "MUNPAL (local admin) civil servants"
	930 "Cooperative societies partners" 
	932 "Temporary 'personal estatutario' in health organization"
	951 "SMC Administrators/partners" 
	980 "Extension of transitory disability"
	999 "Others";
#d cr
	* employer id
#d;
label def empidtype 
	1 "personal id (DNI)" 
	2 "Passport" 
	3 "EU Resident card"
	4 "Working visa" 
	5 "Documents (several)" 
	6 "Foreign id (NIE)"
	7 "Contribution code (CCC)" 
	8 "self-employed id" 
	9 "Fiscal identified (NIF/CIF)"
	0 "Other physical persons or indiv employers";
#d cr
	* legal form
#d;
label def legalform 
	1 "SA" 
	2 "SL" 
	3 "Collectively owned societies" 
	4 "Sociedades comanditarias"
	5 "Comunidades de bienes y herencias yacentes" 
	6 "Cooperative societies"
	7 "Associations and others" 
	8 "Comunidades de propietarios en régimen de propiedad horizontal"
	9 "Civil societies" 
	10 "Foreign entities" 
	11 "Local corporations"
	12 "Public organizations" 
	13 "Religious institutions"
	14 "State and CCAA organisms" 
	15 "Temporary Union of Firms (UTE)"
	16 "Others" 
	17 "Permanent establishments from non-resident entities";
#d cr
	}
* cleaning
forval year = 2017(-1)2004 {

* load
use "$data/temp/mcvl`year'_02affi.dta", clear
* rename and label
rename v1 id
gen contregime = 1 if v2 < 200 & v2 != .
replace contregime = 2 if v2 >= 500 & v2 < 600
replace contregime = 3 if v2 >= 600 & v2 < 700
replace contregime = 4 if v2 >= 700 & v2 < 800
replace contregime = 5 if v2 >= 800 & v2 < 900
replace contregime = 6 if v2 >= 900 & v2 < 1000
replace contregime = 7 if v2 >= 1200 & v2 < 1300
rename v2 contregimeraw
label var contregimeraw "2.002 contribution regime"
label var contregime "contribution regime (from 2.002)"
label val contregime contregime
rename v3 contgroup
label var contgroup "2.003 contribution group"
label val contgroup contgroup
gen contract = 1 if v4 == 0
replace contract = 2 if (v4 >0 & v4 < 100) 
replace contract = 3 if v4 >= 100 & v4 < 300
replace contract = 4 if v4 >= 300 & v4 < 400
replace contract = 5 if v4 >= 400 & v4 < 600
replace contract = 6 if v4 == 990
rename v4 contractraw
la var contractraw "2.004 type of contact"
label var contract "contract (from 2.004)"
label define contract 1 "NA" 2 "Formation (old)" 3 "Indefinite" 4 "Fijo discontinuo" /*
*/ 5 "Temporary" 6 "Others"
label val contract contract
rename v5 partialcoeff
label var partialcoeff "2.005 part-time employment coefficient"
rename v6 affstart
label var affstart "2.006 affiliation start date"
rename v7 affend
label var affend "2.007 affiliation end date"
rename v8 reasonend
label var reasonend "2.008 reason for affiliation termination"
label val reasonend reasoend20072013
rename v9 disability
label var disability "2.009 degree of disability"
rename v10 ccc
label var ccc "2.010 contribution code (ccc)"
rename v11 ccc_mun`year'
label var ccc_mun`year' "2.011 contribution code municipality in `year'"
rename v12 industry
label var industry "2.012 industry of the CCC (CNAE-2009)"
rename v13 emplccc`year'
label var emplccc`year' "2.013 num
rename v14 firstempl
label var firstempl "2.014 date of affiliation of the first employee in the ccc"
rename v15 reltype
label var reltype "2.015 type of labor relationship (trl)"
label val reltype reltype
rename v16 specialccc
label var specialccc "2.016 cc belonging to special group"
rename v17 empidtype
label var empidtype "2.017 type of employer id"
destring empidtype, replace force
label val empidtype empidtype
gen legalform = 1 if v18 == "A"
replace legalform = 2 if v18 == "B"
replace legalform = 3 if v18 == "C"
replace legalform = 4 if v18 == "D"
replace legalform = 5 if v18 == "E"
replace legalform = 6 if v18 == "F"
replace legalform = 7 if v18 == "G"
replace legalform = 8 if v18 == "H"
replace legalform = 9 if v18 == "J"
replace legalform = 10 if v18 == "N"
replace legalform = 11 if v18 == "P"
replace legalform = 12 if v18 == "Q"
replace legalform = 13 if v18 == "R"
replace legalform = 14 if v18 == "S"
replace legalform = 15 if v18 == "U"
replace legalform = 16 if v18 == "V"
replace legalform = 17 if v18 == "W"
replace legalform = 99 if legalform == .
drop v18
label var legalform "2.018 legal form of the employer (based on NIF/CIF)"
label val legalform legalform
rename v19 nif
label var nif "2.019 NIF/CIF - legal entity identifier"
rename v20 firmid
label var firmid "2.020 firm identifier (CCCP)"
	if `year' >= 2005 {
rename v21 firmprovince
label var firmprovince "2.021 province of the firm (CCCP)"
label val firmprovince province
rename v22 modificationdate
label var modificationdate "2.022 date of contract modification (if any)"
destring v23, replace
gen initialcontract = 1 if v23 == 0
replace initialcontract = 2 if (v23 >0 & v23 < 100) 
replace initialcontract = 3 if v23 >= 100 & v23 < 400
replace initialcontract = 4 if v23 >= 400 & v23 < 600
replace initialcontract = 5 if v23 == 990
rename v23 initialcontractraw
label var initialcontractraw "2.023 type of initial contract (if ever modified)"
label var initialcontract "type of initial contract (if ever modified) - from 2.023"
label val initialcontract contract
rename v24 initialptime
label var initialptime "2.024 initial part-time coefficient (if ever modified)"
rename v25 modifdate
label var modifdate "2.025 contract/part-time coeff modification date (if ever modified)"
destring v26, replace
gen secondcontract = 1 if v26 == 0
replace secondcontract = 2 if (v26 >0 & v26 < 100) 
replace secondcontract = 3 if v26 >= 100 & v26 < 400
replace secondcontract = 4 if v26 >= 400 & v26 < 600
replace secondcontract = 5 if v26 == 990
rename v26 secondcontractraw
la var secondcontractraw "2.026 type of second contract (if contract ever modified at least twice)"
label var secondcontract "type of second contract (if contract ever modified at least twice) - from 2.026"
label val secondcontract contract
rename v27 secondptime
label var secondptime "2.027 second part-time coefficient (if modified twice)"
rename v28 cccmodate
label var cccmodate "2.028 contribution group modification date (if any)"
cap destring v29, g(initialccc)
cap g initialccc = v29 
drop v29
label var initialccc "2.029 initial contribution group (if any modification)"
label val initialccc contgroup
	}	
	if `year' >= 2010 {
rename v30 industry93
label var industry93 "2.030 industry (CNAE-93)"
	}
	if `year' >= 2013 {
gen seta = 1 if v31 == "S"
replace seta = 0 if v31 == "N"
drop v31
label var seta "2.031 special system of agrarian workers (SETA)"
label def seta 1 "yes" 0 "no"
label val seta seta
rename v32 selfemplrel
label var selfemplrel "2.032 type of relation (of the self-employed) with other entities or other self-employed"
rename v33 effaffstart
label var effaffstart "2.033 effective registration date"
rename v34 effaffend
label var effaffend "2.034 effective affiliation termination date"
	}
	if `year' == 2004 {
	replace reltype = 0 if reltype == 999
	}
* store
sort id
compress
save "$data/mcvl`year'_02affi.dta", replace
cap erase "$data/temp/mcvl`year'_02affi.dta"
}

** 2004-16
forval year = 2015(-1)2004 {
use "$data/mcvl`year'_02affi.dta", clear
merge m:1 id using "$data/temp/mcvl2004-2016_all_ids.dta", keepusing(mcvl)
keep if mcvl==`year'
drop emplccc* ccc_mun*
drop _m mcvl
save "$data/temp/mcvl`year'_02affi_restid.dta", replace
}
use "$data/mcvl2016_02affi.dta", clear
forval year = 2015(-1)2004 {
append using "$data/temp/mcvl`year'_02affi_restid.dta", force
}
drop emplc* ccc_m*
merge m:1 ccc using "$data/temp/mcvl2004-16_02affi_emplccc.dta"
drop if _merge==2
drop _merge
sort id affstart
compress
save "$data/mcvl2004-16_02affi.dta", replace

forval year = 2015(-1)2004 {
cap erase "$data/temp/mcvl`year'_02affi_restid.dta"
}

** add 2017
forval year = 2016(-1)2004 {
use "$data/mcvl`year'_02affi.dta", clear
merge m:1 id using "$data/temp/mcvl2004-2017_all_ids.dta", keepusing(mcvl)
keep if mcvl==`year'
drop emplccc* ccc_mun*
drop _m mcvl
save "$data/temp/mcvl`year'_02affi_restid.dta", replace
}
use "$data/mcvl2017_02affi.dta", clear
forval year = 2016(-1)2004 {
append using "$data/temp/mcvl`year'_02affi_restid.dta", force
}
drop emplc* ccc_m*
merge m:1 ccc using "$data/temp/mcvl2004-17_02affi_emplccc.dta"
drop if _merge==2
drop _merge
sort id affstart
compress
save "$data/mcvl2004-17_02affi.dta", replace

forval year = 2017(-1)2004 {
cap erase "$data/temp/mcvl`year'_02affi_restid.dta"
cap erase "$data/temp/mcvl`year'_02affi_emplccc.dta"
cap erase "$data/mcvl`year'_02affi.dta"
}
cap erase "$data/temp/mcvl2004-17_02affi_emplccc.dta"
cap erase "$data/temp/mcvl2004-16_02affi_emplccc.dta"

}
********************************************************************************
* table 3 - contributions
********************************************************************************
{

** MCVL 2017-2013
forval year = 2017(-1)2013 { 
foreach num of numlist 1/13 {
	
* import
clear
insheet using "$orig/MCVL-`year' CDF/cont/MCVL`year'COTIZA`num'_CDF.TXT", delimiter(";") 
compress
save "$data/temp/mcvl`year'_03cont_`num'.dta", replace 
}
* append
use "$data/temp/mcvl`year'_03cont_1.dta", clear
foreach num of numlist 2/13 {
append using "$data/temp/mcvl`year'_03cont_`num'.dta"
}
* store
rename v1 id
sort id
compress
save "$data/temp/mcvl`year'_03cont.dta", replace 
* erase intermediate files
foreach num of numlist 1/13 {
cap erase "$data/temp/mcvl`year'_03cont_`num'.dta"
}
} 

** MCVL 2012-2010
forval year = 2012(-1)2010 { 
foreach num of numlist 1/13 {
* import data
clear
insheet using "$orig/MCVL-`year' CDF/cont/MCVL`year'COTIZA`num'_CDF.TXT", delimiter(";") 
compress
save "$data/temp/mcvl`year'_03cont_`num'.dta", replace 
}
* append
use "$data/temp/mcvl`year'_03cont_1.dta", clear
foreach num of numlist 2/13 {
append using "$data/temp/mcvl`year'_03cont_`num'.dta"
}
* store
rename v1 id
sort id
compress
save "$data/temp/mcvl`year'_03cont_t3oldformat.dta", replace 
* erase intermediate files
foreach num of numlist 1/13 {
cap erase "$data/temp/mcvl`year'_03cont_`num'.dta"
}
} 

** MCVL 2009 
forval year = 2009(-1)2009 { 
foreach num of numlist 1/13 {
* import 
clear
insheet using "$orig/MCVL-`year' CDF/cont/MCVL`year'COTIZA`num'.TXT", delimiter(";") 
compress
save "$data/temp/mcvl`year'_03cont_`num'.dta", replace 
}
* append
use "$data/temp/mcvl`year'_03cont_1.dta", clear
foreach num of numlist 2/13 {
append using "$data/temp/mcvl`year'_03cont_`num'.dta"
}
* store
rename v1 id
sort id
compress
save "$data/temp/mcvl`year'_03cont_t3oldformat.dta", replace 
* erase intermediate files
foreach num of numlist 1/13 {
cap erase "$data/temp/mcvl`year'_03cont_`num'.dta"
}
} 

** MCVL 2008-2006
forval year = 2008(-1)2006 { 
foreach num of numlist 1/13 {
* import
clear
insheet using "$orig/MCVL-`year' CDF/cont/COTIANON`num'.trs", delimiter(";") 
compress
save "$data/temp/mcvl`year'_03cont_`num'.dta", replace 
}
* append
use "$data/temp/mcvl`year'_03cont_1.dta", clear
foreach num of numlist 2/13 {
append using "$data/temp/mcvl`year'_03cont_`num'.dta"
}
* store
rename v1 id
sort id
compress
save "$data/temp/mcvl`year'_03cont_t3oldformat.dta", replace 
* erase intermediate files
foreach num of numlist 1/13 {
cap erase "$data/temp/mcvl`year'_03cont_`num'.dta"
}
} 

** MCVL 2005 
forval year = 2005(-1)2005 { 
foreach num of numlist 1/12 {
* import
clear
insheet using "$orig/MCVL-`year' CDF/cont/COTIANON`num'.trs", delimiter(";") 
compress
save "$data/temp/mcvl`year'_03cont_`num'.dta", replace 
}
* append
use "$data/temp/mcvl`year'_03cont_1.dta", clear
foreach num of numlist 2/12 {
append using "$data/temp/mcvl`year'_03cont_`num'.dta"
}
* store
rename v1 id
sort id
compress
save "$data/temp/mcvl`year'_03cont_t3oldformat.dta", replace 
* erase intermediate files
foreach num of numlist 1/12 {
cap erase "$data/temp/mcvl`year'_03cont_`num'.dta"
}
} 

** MCVL 2004 
foreach num of numlist 1/12 {
* import data
clear
import delimited "$orig/MCVL-2004 CDF/cont/COTIANON`num'.txt", delimiter(whitespace)
compress
save "$data/temp/mcvl2004_03cont_`num'.dta", replace 
}
* append
use "$data/temp/mcvl2004_03cont_1.dta", clear
foreach num of numlist 2/12 {
append using "$data/temp/mcvl2004_03cont_`num'.dta"
}
* specific to 2004
drop v21 v22 v23 v24 v25 v26 v27 v28 v29 v30 v31 v32 v34
rename v33 v21
rename v35 v22
* store
rename v1 id
sort id
compress
save "$data/temp/mcvl2004_03cont_t3oldformat.dta", replace 
* erase intermediate files
foreach num of numlist 1/12 {
cap erase "$data/temp/mcvl2004_03cont_`num'.dta"
}

** MCVL 2005-2012 to new format
forval year = 2012(-1)2004 {
use "$data/temp/mcvl`year'_03cont_t3oldformat.dta", clear
drop v3 v4 v5 v6 v7 v22
rename v8 contyear
rename v9 cotbjan
rename v10 cotbfeb
rename v11 cotbmar
rename v12 cotbapr
rename v13 cotbmay
rename v14 cotbjun
rename v15 cotbjul
rename v16 cotbaug
rename v17 cotbsep
rename v18 cotboct
rename v19 cotbnov
rename v20 cotbdec
drop if (contyear < 1965 | contyear > 2012)
egen tagidyear = tag(id contyear)
gen x = 1 if _n==1
replace x = x[_n-1] + tagidyear if _n != 1

foreach month in jan feb mar apr may jun jul aug sep oct nov dec {
bysort x: egen maxcotb`month' = max(cotb`month')
drop cotb`month'
rename maxcotb`month' cotb`month'
}
drop x tagidyear
gen v16 = cotbjan+cotbfeb+cotbmar+cotbapr+cotbmay+cotbjun+cotbjul+cotbaug+/*
*/            cotbsep+cotboct+cotbnov+cotbdec
gsort id contyear -v16
duplicates drop id contyear, force
drop v21
rename contyear v3
rename cotbjan v4
rename cotbfeb v5
rename cotbmar v6
rename cotbapr v7
rename cotbmay v8
rename cotbjun v9
rename cotbjul v10
rename cotbaug v11
rename cotbsep v12
rename cotboct v13
rename cotbnov v14
rename cotbdec v15
sort id v3
compress
save "$data/temp/mcvl`year'_03cont.dta", replace
* erase data in old format
cap erase "$data/temp/mcvl`year'_03cont_t3oldformat.dta"
}

** put data together
forval year = 2016(-1)2004 {
use "$data/temp/mcvl`year'_03cont.dta", clear
merge m:1 id using "$data/temp/mcvl2004-2017_all_ids.dta", keepusing(mcvl)
keep if mcvl==`year'
drop _m mcvl
save "$data/temp/mcvl`year'_03cont_restid.dta", replace
}
use "$data/temp/mcvl2017_03cont.dta", clear
forval year = 2016(-1)2004 {
append using "$data/temp/mcvl`year'_03cont_restid.dta", force
}
forval year = 2016(-1)2004 {
cap erase "$data/temp/mcvl`year'_03cont_restid.dta"
}

* rename and label
rename v2 ccc
rename v3 contyear
label var contyear "3.003 year of contribution"
rename v4 cotbjan
label var cotbjan "3.004 contribution base (january)"
rename v5 cotbfeb
label var cotbfeb "3.005 contribution base (february)"
rename v6 cotbmar
label var cotbmar "3.006 contribution base (march)"
rename v7 cotbapr
label var cotbapr "3.007 contribution base (april)"
rename v8 cotbmay
label var cotbmay "3.008 contribution base (may)"
rename v9 cotbjun
label var cotbjun "3.009 contribution base (june)"
rename v10 cotbjul
label var cotbjul "3.010 contribution base (july)"
rename v11 cotbaug
label var cotbaug "3.011 contribution base (august)"
rename v12 cotbsep
label var cotbsep "3.012 contribution base (september)"
rename v13 cotboct
label var cotboct "3.013 contribution base (october)"
rename v14 cotbnov
label var cotbnov "3.014 contribution base (november)"
rename v15 cotbdec
label var cotbdec "3.015 contribution base (december)"
rename v16 cotbtot
label var cotbtot "3.016 contribution base (annual)"

* store
sort id contyear
compress
save "$data/mcvl2004-17_03cont.dta", replace

* erase temp data
forval year = 2017(-1)2004 {
cap erase "$data/temp/mcvl`year'_03cont.dta"
}

}
********************************************************************************
* table 4 - pensions
********************************************************************************
{

** MCVL 2017-2009
forval year = 2017(-1)2009 {
clear 
insheet using "$orig/MCVL-`year' CDF/pens/MCVL`year'PRESTAC_CDF.TXT", delimiter(";") 
* rename
rename v1 id
sort id
* store
compress
save "$data/temp/mcvl`year'_04pens.dta", replace 
}

** MCVL 2008-2005
forval year = 2008(-1)2005 {
clear 
insheet using "$orig/MCVL-`year' CDF/pens/PREANON.trs", delimiter(";") 
* rename
rename v1 id
sort id
* store
compress
save "$data/temp/mcvl`year'_04pens.dta", replace 
}

** put data together
forval year = 2016(-1)2005 {
use "$data/temp/mcvl`year'_04pens.dta", clear
merge m:1 id using "$data/temp/mcvl2004-2017_all_ids.dta", keepusing(mcvl)
keep if mcvl==`year'
drop _m mcvl
save "$data/temp/mcvl`year'_04pens_restid.dta", replace
}
use "$data/temp/mcvl2017_04pens.dta", clear
forval year = 2016(-1)2005 {
append using "$data/temp/mcvl`year'_04pens_restid.dta", force
cap erase "$data/temp/mcvl`year'_04pens_restid.dta"
}

* rename and label
rename v2 year
label var year "4.002 year"
rename v3 pensionid
label var pensionid "4.003 subsidy identifier"
gen t = 1 if v4 == "J1"
replace t = 2 if v4 == "J2"
replace t = 3 if v4 == "J3"
replace t = 4 if v4 == "J4"
replace t = 5 if v4 == "J5"
replace t = 10 if v4 == "10"
replace t = 11 if v4 == "11"
replace t = 12 if v4 == "12"
replace t = 13 if v4 == "13"
replace t = 14 if v4 == "14"
replace t = 15 if v4 == "15"
replace t = 16 if v4 == "16"
replace t = 17 if v4 == "17"
replace t = 18 if v4 == "18"
replace t = 19 if v4 == "19"
replace t = 20 if v4 == "20"
replace t = 21 if v4 == "21"
replace t = 22 if v4 == "22"
replace t = 23 if v4 == "23"
replace t = 24 if v4 == "24"
replace t = 25 if v4 == "25"
replace t = 26 if v4 == "26"
replace t = 30 if v4 == "30"
replace t = 31 if v4 == "31"
replace t = 32 if v4 == "32"
replace t = 40 if v4 == "40"
replace t = 41 if v4 == "41"
replace t = 42 if v4 == "42"
replace t = 51 if v4 == "51"
replace t = 52 if v4 == "52"
replace t = 53 if v4 == "53"
replace t = 54 if v4 == "54"
rename t pensiontype
drop v4
label var pensiontype "4.004 pension type"
#d;
label def pensiontype 
	1 "absolute disability (>65)" 
	2 "total disability (>65)" 
	3 "partial permanent disability (>65)"
	4 "grand disability (>65)" 
	5 "complementary disability silicosis (>65)" 
	10 "temporal disability" 
	11 "absolute disability"
	12 "total disability" 
	13 "permanent partial disability" 
	14 "grand disability" 
	15 "complementary disability silicosis" 
	16 "temporal disability (long disease)"
	17 "temporal disability (AT & EP)" 
	18 "other degrees of disability" 
	19 "other degrees of disability" 
	20 "provisional retirement" 
	21 "general retirement"
	22 "early retirement" 
	23 "subsidies prior to retirement" 
	24 "special retirement at 64" 
	25 "partial retirement" 
	26 "retirement SOVI"
	30 "provisional widowhood" 
	31 "general widowhood" 
	32 "MUNPAL widowhood" 
	40 "provisional orphanhood" 
	41 "general orphanhood" 
	42 "absolute orphanhood"
	51 "in favor of relatives" 
	52 "in favor of relatives" 
	53 "in favor of relatives"
	54 "in favor of relatives";
#d cr
label val pensiontype pensiontype
drop v5
rename v6 distype
label var distype "4.006 disability degree"
#d;
label def distype 
	1 "great disability" 
	2 "absolute incapacity"
	3 "total incapacity for usual jon (55%)" 
	4 "total incapacity for usual jon (75%)"
	5 "permanent partial incapacity" 
	6 "other incapacity pensions";
#d cr
label val distype distype
rename v7 disdate
label var disdate "4.007 disability recognition date"
drop v8
rename v9 mintype
label var mintype "4.009 minimum type"
#d;
label def mintype 
	0 "NA" 
	1 "min without spouse" 
	2 "min with spouse"
	3 "min with spouse (1993)" 
	4 "min with family responsabilities"
	5 "min disability" 
	6 "min with responsabilities and disability"
	7 "min with spouse but no responsability" 
	8 "min unipersonal";
#d cr
label val mintype mintype
rename v10 penreg
label var penreg "4.010 pension regime"
label def penreg 1 "general" 5 "special for self-employed" 6 "agrarian (general)" /*
*/ 7 "agrarian (self-employed)" 8 "special (sea workers)" 9 "special (mining)" /*
*/ 12 "special (domestic workers)" 31 "social assistance" 32 "social assistance complement" /*
*/ 35 "ism social assiatnce complement" 36 ""
label val penreg penreg
rename v11 pendate
label var pendate "4.011 pension start date"
rename v12 regbase
label var regbase "4.012 regulation base (amount)"
rename v13 percbase
label var percbase "4.013 percentage applied to the regulation base"
rename v14 subyears
label var subyears "4.014 subsidized years"
rename v15 contyears
label var contyears "4.015 years of contribution"
rename v16 pensbase
label var pensbase "4.016 effective pension base (monthly) - without complements"
rename v17 penreval
label var penreval "4.017 pension revalorization (monthly)"
rename v18 pencompl
label var pencompl "4.018 guaranteed amount complemente (monthly)"
rename v19 penothers
label var penothers "4.019 other pension complements (monthly)"
rename v20 pentotal 
label var pentotal "4.020 pension amount (monthly) - with complements"
gen penstatus = 1 if (v21 == 0 | v21 == 2 | v21 == 3)
replace penstatus = 2 if (v21 >= 100 & v21 < 200)
replace penstatus = 3 if (v21 >= 200 & v21 != .)
drop v21
label var penstatus "4.021 pension status"
label def penstatus 1 "'alta'" 2 "'baja'" 3 "suspended"
label val penstatus penstatus
rename v22 penstatdate
label var penstatdate "4.022 pension status modification date"
rename v23 penadmpro
label var penadmpro "4.023 provice of the pension administration"
label val penadmpro province
drop v24
drop v25
drop v26
rename v27 redcoef
label var redcoef "4.027 reduction coeffiecient applied to reg base due to early retirement"
rename v28 retstat
label var retstat "4.028 retirement status"
label def retstat 0 "other" 1 "early" 2 "early (non-voluntary)" /*
*/ 3 "early (non-voluntary)" 4 "early (non-voluntary)" 5 "flexible" /*
*/ 6 "partial" 7 "early" 8 "early (non-voluntary)" 9 "early (collective agreement)" /*
*/ 10 "early" 11 "early (non-voluntary)" 12 "early (non-voluntary)" /*
*/ 13 "early (collective agreement)" 14 "early (non-voluntary)" /*
*/ 15 "late (voluntary)" 16 "late (due to partial ret)" 17 "early (disabilty)" /*
*/ 18 "early (non-voluntary)" 19 "early" 20 "normal" 21 "early"
label val retstat retstat
rename v29 rpartcoef
label var rpartcoef "4.029 partial retirement coefficient"
rename v30 penperp
label var penperp "4.030 life pension"
label def penperp 0 "not orphanhood or widowhood" 1 "orphanhood or widowhood but not perpetual" /*
*/ 2 "orphanhood or widowhood and perpetual"
label val penperp penperp
rename v31 penother
label var penother "4.031 additional (in addition to SS) pension"
label def penother 0 "no" 1 "yes"
label val penother penother
rename v32 penextra
label var penextra "4.032 special pension payment amount (yearly)"
rename v33 peninfl
label var peninfl "4.033 inflation compensation"
rename v34 pentoty
label var pentoty "4.034 pension total (yearly)"
rename v35 pensury
label var pensury "4.035 pension survival recognition year"
rename v36 penlim
label var penlim "4.036 limited pension"
label def penlim 0 "no" 5 "yes (guaranteed amount)" 7 "yes (orphanhood)" /*
*/ 1 "yes" 2 "yes" 3 "yes" 4 "yes" 6 "yes" 8 "yes" 9 "yes"
label val penlim penlim
rename v37 limcoef
label var limcoef "4.037 reduction coefficient of the top limit"
rename v38 workretc
label var workretc "4.038 work and retirement compatibility"
rename v39 retdate
label var retdate "4.039 retirement date"
rename v40 contatret
label var contatret "4.040 contribution (months) at retirement"
rename v41 conttime
label var conttime "4.041 contribution time (months)"
rename v42 contperc
label var contperc "4.042 percentage for years contributed"

* store
duplicates drop id year pensionid, force
sort id year
compress
save "$data/mcvl2004-17_04pens.dta", replace

}
********************************************************************************
* table 5 - cohabitants
********************************************************************************
{

** MCVL 2017-2009
forval year = 2017(-1)2009 {

insheet using "$orig/MCVL-`year' CDF/coha/MCVL`year'CONVIVIR_CDF.TXT", delimiter(";") clear

* rename
rename v1 id
rename v2 birthc1_`year'
rename v3 genderc1_`year'
rename v4 birthc2_`year'
gen genderc2_`year'= 1 if v5 == "1"
replace genderc2_`year' = 2 if v5 == "2"
drop v5
rename v6 birthc3_`year'
gen genderc3_`year' = 1 if v7 == "1"
replace genderc3_`year' = 2 if v7 == "2"
drop v7
rename v8 birthc4_`year'
gen genderc4_`year' = 1 if v9 == "1"
replace genderc4_`year' = 2 if v9 == "2"
drop v9
rename v10 birthc5_`year'
gen genderc5_`year' = 1 if v11 == "1"
replace genderc5_`year' = 2 if v11 == "2"
drop v11
rename v12 birthc6_`year'
gen genderc6_`year' = 1 if v13 == "1"
replace genderc6_`year' = 2 if v13 == "2"
drop v13
rename v14 birthc7_`year'
gen genderc7_`year' = 1 if v15 == "1"
replace genderc7_`year' = 2 if v15 == "2"
drop v15
rename v16 birthc8_`year'
gen genderc8_`year' = 1 if v17 == "1"
replace genderc8_`year' = 2 if v17 == "2"
drop v17
rename v18 birthc9_`year'
gen genderc9_`year' = 1 if v19 == "1"
replace genderc9_`year' = 2 if v19 == "2"
drop v19
rename v20 birthc10_`year'
rename v21 genderc10_`year'

* store
sort id
compress
save "$data/temp/mcvl`year'_05coha.dta", replace 
}

** MCVL 2008-2005
forval year = 2008(-1)2005 {
insheet using "$orig/MCVL-`year' CDF/coha/CONVIVI.trs", delimiter(";") clear

* rename
rename v1 id
rename v2 birthc1_`year'
rename v3 genderc1_`year'
rename v4 birthc2_`year'
gen genderc2_`year'= 1 if v5 == "1"
replace genderc2_`year' = 2 if v5 == "2"
drop v5
rename v6 birthc3_`year'
gen genderc3_`year' = 1 if v7 == "1"
replace genderc3_`year' = 2 if v7 == "2"
drop v7
rename v8 birthc4_`year'
gen genderc4_`year' = 1 if v9 == "1"
replace genderc4_`year' = 2 if v9 == "2"
drop v9
rename v10 birthc5_`year'
gen genderc5_`year' = 1 if v11 == "1"
replace genderc5_`year' = 2 if v11 == "2"
drop v11
rename v12 birthc6_`year'
gen genderc6_`year' = 1 if v13 == "1"
replace genderc6_`year' = 2 if v13 == "2"
drop v13
rename v14 birthc7_`year'
gen genderc7_`year' = 1 if v15 == "1"
replace genderc7_`year' = 2 if v15 == "2"
drop v15
rename v16 birthc8_`year'
gen genderc8_`year' = 1 if v17 == "1"
replace genderc8_`year' = 2 if v17 == "2"
drop v17
rename v18 birthc9_`year'
gen genderc9_`year' = 1 if v19 == "1"
replace genderc9_`year' = 2 if v19 == "2"
drop v19
rename v20 birthc10_`year'
rename v21 genderc10_`year'

* store
sort id
compress
save "$data/temp/mcvl`year'_05coha.dta", replace 
}

** put together
use "$data/temp/mcvl2017_05coha.dta", clear

forval year = 2016(-1)2005 {
	merge m:m id using "$data/temp/mcvl`year'_05coha.dta", force
	drop _merge
}
* label
label def gender 1 "male" 2 "female"
forval year = 2017(-1)2005 {
label var birthc1_`year' "5.002 birth date of cohabitant 1 in `year'"
label var genderc1_`year' "5.003 gender of cohabitant 1 in `year'"
label val genderc1_`year' gender
label var birthc2_`year' "5.004 birth date of cohabitant 2 in `year'"
label var genderc2_`year' "5.005 gender of cohabitant 2 in `year'"
label val genderc2_`year' gender
label var birthc3_`year' "5.006 birth date of cohabitant 3 in `year'"
label var genderc3_`year' "5.007 gender of cohabitant 3 in `year'"
label val genderc3_`year' gender
label var birthc4_`year' "5.008 birth date of cohabitant 4 in `year'"
label var genderc4_`year' "5.009 gender of cohabitant 4 in `year'"
label val genderc4_`year' gender
label var birthc5_`year' "5.010 birth date of cohabitant 5 in `year'"
label var genderc5_`year' "5.011 gender of cohabitant 5 in `year'"
label val genderc5_`year' gender
label var birthc6_`year' "5.012 birth date of cohabitant 6 in `year'"
label var genderc6_`year' "5.013 gender of cohabitant 6 in `year'"
label val genderc6_`year' gender
label var birthc7_`year' "5.014 birth date of cohabitant 7 in `year'"
label var genderc7_`year' "5.015 gender of cohabitant 7 in `year'"
label val genderc7_`year' gender
label var birthc8_`year' "5.016 birth date of cohabitant 8 in `year'"
label var genderc8_`year' "5.017 gender of cohabitant 8 in `year'"
label val genderc8_`year' gender
label var birthc9_`year' "5.018 birth date of cohabitant 9 in `year'"
label var genderc9_`year' "5.019 gender of cohabitant 9 in `year'"
label val genderc9_`year' gender
label var birthc10_`year' "5.020 birth date of cohabitant 10 in `year'"
label var genderc10_`year' "5.021 gender of cohabitant 10 in `year'"
label val genderc10_`year' gender
}

* store
sort id
compress
save "$data/mcvl2004-17_05coha.dta", replace

* erase intermediate datasets
forval year = 2017(-1)2004 {
cap erase "$data/temp/mcvl`year'_05coha.dta"
}
}
********************************************************************************
* table 6 - fiscal
********************************************************************************
{

** MCVL 2017
forval year = 2017(1)2017 {
* import
import delimited "$orig/MCVL-`year' CDF/fisc/MCVL`year'FISCAL_CDF.TXT", delimiter(";") clear
* rename
rename v1 id
gen legalform = 1 if v2 == "A"
replace legalform = 2 if v2 == "B"
replace legalform = 3 if v2 == "C"
replace legalform = 4 if v2 == "D"
replace legalform = 5 if v2 == "E"
replace legalform = 6 if v2 == "F"
replace legalform = 7 if v2 == "G"
replace legalform = 8 if v2 == "H"
replace legalform = 9 if v2 == "J"
replace legalform = 10 if v2 == "N"
replace legalform = 11 if v2 == "P"
replace legalform = 12 if v2 == "Q"
replace legalform = 13 if v2 == "R"
replace legalform = 14 if v2 == "S"
replace legalform = 15 if v2 == "U"
replace legalform = 16 if v2 == "V"
replace legalform = 17 if v2 == "W"
replace legalform = 99 if legalform == .
rename legalform legalform_`year'
drop v2 v3
rename v4 province190_`year'
gen retkey = 1 if v5 == "A"
replace retkey = 2 if v5 == "B"
replace retkey = 3 if v5 == "C"
replace retkey = 4 if v5 == "D"
replace retkey = 5 if v5 == "E"
replace retkey = 6 if v5 == "F"
replace retkey = 7 if v5 == "G"
replace retkey = 8 if v5 == "H"
replace retkey = 9 if v5 == "I"
replace retkey = 10 if v5 == "J"
replace retkey = 11 if v5 == "K"
replace retkey = 12 if v5 == "L"
replace retkey = 99 if retkey == .
drop v5
drop v6
rename v7 ret_noDI_`year'
rename v8 ret_DI_`year'
g ret_`year' = ret_noDI_`year' + ret_DI_`year'
rename v9 retentions_noDI_`year'
rename v10 retentions_DI_`year'
g retentions_`year' = retentions_noDI_`year' + retentions_DI_`year'
rename v11 fringe_`year'
rename v12 ingc_`year'
rename v13 ingcr_noDI_`year'
rename v14 ingcr_DI_`year'
g ingcr_`year' = ingcr_noDI_`year' + ingcr_DI_`year'
drop v15-v17
rename v18 birth190_`year'
rename v19 famstat_`year'
rename v20 disability190_`year'
rename v21 reltype190_`year'
rename v22 geomob_`year'
drop v23
drop v24
rename v25 compens_`year'
rename v26 fann_`year'
rename v27 child3_`year'
drop v28
rename v29 childr_`year'
drop v30
rename v31 dischild33_`year'
drop v32
rename v33 dischild33m_`year'
drop v34
rename v35 dischild65_`year'
drop v36
rename v37 child_`year'
rename v38 ancestl75_`year'
drop v39
rename v40 ancestm75_`year'
drop v41
rename v42 disanc33_`year'
drop v43
rename v44 disanc33m_`year'
drop v45
rename v46 disanc65_`year'
drop v47
rename v48 ancest_`year'
* collpase at id retkey level 
order id retkey* ret_* retentions* fringe* ing*, first 
collapse (sum) ret_* retentions* fringe* ing* (first) province birth-ancest_, by(id retkey)
* store
sort id retkey
compress
save "$data/temp/mcvl`year'_06fisc.dta", replace 
}

** MCVL 2016
forval year = 2016(1)2016 {
* import
import delimited "$orig/MCVL-`year' CDF/fisc/MCVL`year'FISCAL_CDF.TXT", delimiter(";") clear
* rename 
rename v1 id
drop v2
rename v4 province190_`year'
gen retkey = 1 if v5 == "A"
replace retkey = 2 if v5 == "B"
replace retkey = 3 if v5 == "C"
replace retkey = 4 if v5 == "D"
replace retkey = 5 if v5 == "E"
replace retkey = 6 if v5 == "F"
replace retkey = 7 if v5 == "G"
replace retkey = 8 if v5 == "H"
replace retkey = 9 if v5 == "I"
replace retkey = 10 if v5 == "J"
replace retkey = 11 if v5 == "K"
replace retkey = 12 if v5 == "L"
replace retkey = 99 if retkey == .
drop v5
drop v6
rename v7 ret_noDI_`year'
rename v8 ret_DI_`year'
g ret_`year' = ret_noDI_`year' + ret_DI_`year'
rename v9 retentions_noDI_`year'
rename v10 retentions_DI_`year'
g retentions_`year' = retentions_noDI_`year' + retentions_DI_`year'
rename v11 fringe_`year'
rename v12 ingc_`year'
rename v13 ingcr_noDI_`year'
rename v14 ingcr_DI_`year'
g ingcr_`year' = ingcr_noDI_`year' + ingcr_DI_`year'
drop v15
rename v16 birth190_`year'
rename v17 famstat_`year'
rename v18 disability190_`year'
rename v19 reltype190_`year'
rename v20 geomob_`year'
drop v21
drop v22
rename v23 compens_`year'
rename v24 fann_`year'
rename v25 child3_`year'
drop v26
rename v27 childr_`year'
drop v28
rename v29 dischild33_`year'
drop v30
rename v31 dischild33m_`year'
drop v32
rename v33 dischild65_`year'
drop v34
rename v35 child_`year'
rename v36 ancestl75_`year'
drop v37
rename v38 ancestm75_`year'
drop v39
rename v40 disanc33_`year'
drop v41
rename v42 disanc33m_`year'
drop v43
rename v44 disanc65_`year'
drop v45
rename v46 ancest_`year'
* collpase at id retkey level 
order id retkey* ret_* retentions* fringe* ing*, first 
collapse (sum) ret_* retentions* fringe* ing* (first) province birth-ancest_, by(id retkey)
* store data
sort id
compress
save "$data/temp/mcvl`year'_06fisc.dta", replace 
}

** MCVL 2015-2009
forval year = 2015(-1)2009 {
* import
import delimited "$orig/MCVL-`year' CDF/fisc/MCVL`year'FISCAL_CDF.TXT", delimiter(";") clear
* rename
rename v1 id
drop v2
rename v4 province190_`year'
gen retkey = 1 if v5 == "A"
replace retkey = 2 if v5 == "B"
replace retkey = 3 if v5 == "C"
replace retkey = 4 if v5 == "D"
replace retkey = 5 if v5 == "E"
replace retkey = 6 if v5 == "F"
replace retkey = 7 if v5 == "G"
replace retkey = 8 if v5 == "H"
replace retkey = 9 if v5 == "I"
replace retkey = 10 if v5 == "J"
replace retkey = 11 if v5 == "K"
replace retkey = 12 if v5 == "L"
replace retkey = 99 if retkey == .
drop v5
drop v6
rename v7 ret_`year'
rename v8 retentions_`year'
rename v9 fringe_`year'
rename v10 ingc_`year'
rename v11 ingcr_`year'
drop v12
rename v13 birth190_`year'
rename v14 famstat_`year'
rename v15 disability190_`year'
rename v16 reltype190_`year'
drop v17
rename v18 geomob_`year'
drop v19
drop v20
rename v21 compens_`year'
rename v22 fann_`year'
rename v23 child3_`year'
drop v24
rename v25 childr_`year'
drop v26
rename v27 dischild33_`year'
drop v28
rename v29 dischild33m_`year'
drop v30
rename v31 dischild65_`year'
drop v32
rename v33 child_`year'
rename v34 ancestl75_`year'
drop v35
rename v36 ancestm75_`year'
drop v37
rename v38 disanc33_`year'
drop v39
rename v40 disanc33m_`year'
drop v41
rename v42 disanc65_`year'
drop v43
rename v44 ancest_`year'
* collpase at id retkey level 
order id retkey* ret_* retentions* fringe* ing*, first 
collapse (sum) ret_* retentions* fringe* ing* (first) province birth-ancest_, by(id retkey)
* store
sort id
compress
save "$data/temp/mcvl`year'_06fisc.dta", replace 
}

** MCVL 2008-2006
forval year = 2008(-1)2006 {
* import
clear 
import delimited "$orig/MCVL-`year' CDF/fisc/DATOS_FISCALES.trs", delimiter(";")
* rename
rename v1 id
drop v2
rename v4 province190_`year'
gen retkey = 1 if v5 == "A"
replace retkey = 2 if v5 == "B"
replace retkey = 3 if v5 == "C"
replace retkey = 4 if v5 == "D"
replace retkey = 5 if v5 == "E"
replace retkey = 6 if v5 == "F"
replace retkey = 7 if v5 == "G"
replace retkey = 8 if v5 == "H"
replace retkey = 9 if v5 == "I"
replace retkey = 10 if v5 == "J"
replace retkey = 11 if v5 == "K"
replace retkey = 12 if v5 == "L"
replace retkey = 99 if retkey == .
drop v5
drop v6
rename v7 ret_`year'
rename v8 retentions_`year'
rename v9 fringe_`year'
rename v10 ingc_`year'
rename v11 ingcr_`year'
drop v12
rename v13 birth190_`year'
rename v14 famstat_`year'
rename v15 disability190_`year'
rename v16 reltype190_`year'
drop v17
rename v18 geomob_`year'
drop v19
drop v20
rename v21 compens_`year'
rename v22 fann_`year'
rename v23 child3_`year'
drop v24
rename v25 childr_`year'
drop v26
rename v27 dischild33_`year'
drop v28
rename v29 dischild33m_`year'
drop v30
rename v31 dischild65_`year'
drop v32
rename v33 child_`year'
rename v34 ancestl75_`year'
drop v35
rename v36 ancestm75_`year'
drop v37
rename v38 disanc33_`year'
drop v39
rename v40 disanc33m_`year'
drop v41
rename v42 disanc65_`year'
drop v43
rename v44 ancest_`year'
* collpase at id retkey level 
order id retkey* ret_* retentions* fringe* ing*, first 
collapse (sum) ret_* retentions* fringe* ing* (first) province birth-ancest_, by(id retkey)
* store
sort id
compress
save "$data/temp/mcvl`year'_06fisc.dta", replace 
}

** MCVL 2005
forval year = 2005(-1)2005 {
* import
clear 
import delimited "$orig/MCVL-`year' CDF/fisc/BLOQUE5.trs", delimiter(";")
* rename
rename v1 id
drop v2
rename v4 province190_`year'
gen retkey = 1 if v5 == "A"
replace retkey = 2 if v5 == "B"
replace retkey = 3 if v5 == "C"
replace retkey = 4 if v5 == "D"
replace retkey = 5 if v5 == "E"
replace retkey = 6 if v5 == "F"
replace retkey = 7 if v5 == "G"
replace retkey = 8 if v5 == "H"
replace retkey = 9 if v5 == "I"
replace retkey = 10 if v5 == "J"
replace retkey = 11 if v5 == "K"
replace retkey = 12 if v5 == "L"
replace retkey = 99 if retkey == .
drop v5
drop v6
rename v7 ret_`year'
rename v8 retentions_`year'
rename v9 fringe_`year'
rename v10 ingc_`year'
rename v11 ingcr_`year'
drop v12
rename v13 birth190_`year'
rename v14 famstat_`year'
rename v15 disability190_`year'
rename v16 reltype190_`year'
drop v17
rename v18 geomob_`year'
drop v19
drop v20
rename v21 compens_`year'
rename v22 fann_`year'
rename v23 child3_`year'
drop v24
rename v25 childr_`year'
drop v26
rename v27 dischild33_`year'
drop v28
rename v29 dischild33m_`year'
drop v30
rename v31 dischild65_`year'
drop v32
rename v33 child_`year'
rename v34 ancestl75_`year'
drop v35
rename v36 ancestm75_`year'
drop v37
rename v38 disanc33_`year'
drop v39
rename v40 disanc33m_`year'
drop v41
rename v42 disanc65_`year'
drop v43
rename v44 ancest_`year'
* collpase at id retkey level 
order id retkey* ret_* retentions* fringe* ing*, first 
collapse (sum) ret_* retentions* fringe* ing* (first) province birth-ancest_, by(id retkey)
* store
sort id
compress
save "$data/temp/mcvl`year'_06fisc.dta", replace 
}

*** define labels
* retention key
#d;
label def retkey 
	1 "labor returns: general employed" 
	2 "labor returns: pensioners"
	3 "labor returns: UI" 
	4 "labor returns: UI (unique payment)"
	5 "labor returns: advisers and administrators" 
	6 "labor returns: courses and similar"
	7 "economic activities: professional activities" 
	8 "economic activities: agriculture"
	9 "economic activities: related to article 75.2b from 'reglamento impuesto'"
	10 "image rights" 
	11 "benefits from forestal activities in public terrain"
	12 "exempt rents";
#d cr
* family status
#d;
label def famstat 
	1 "single/widowed/divorced, with children under 18 or above with disability"
	2 "married with spouse earning less than 1500€ per year" 
	3 "other (married with spouse earning €1500+ or single/widowed/divorced without children)";
#d cr
* disability
#d;
label def disability190 
	0 "[0,33)" 
	1 "[33,65)" 
	2 "[33,65) and requires assistance"
	3 "[65,99]";
#d cr
* relationtype
#d;
label def reltype190 
	0 "" 
	1 "general" 
	2 "temporary (<1 year)" 
	3 "dependant" 
	4 "sporadic";
#d cr
* geomob
label def geomob 1 "yes" 0 "no"

** finish cleaning each year
forval year = 2017(-1)2005 {

use "$data/temp/mcvl`year'_06fisc.dta", clear

cap destring reltype190_*, replace
cap destring disability190_*, replace
cap destring geomob_*, replace
label var province190_`year' "6.004 individual province in `year'"
label var retkey "6.005 retribution key"
label val retkey retkey
label var ret_`year' "6.007 full retribution (cents) in `year'"
label var retentions_`year' "6.008 retentions (cents) in `year'"
label var fringe_`year' "6.009 valuation of the fringe benefits (cents) in `year'"
label var ingc_`year' "6.010 'a cuenta' income (cents) in `year'"
label var ingcr_`year' "6.011 'a cuenta' income 'repercutido' (cents) in `year'"
label var birth190_`year' "6.013 birth date (from modelo 190) in `year'"
label var famstat_`year' "6.014 family status in `year'"
label val famstat_`year' famstat
label var disability190_`year' "6.015 disability (from modelo 190) in `year'"
label val disability190_`year' disability190
label var reltype190_`year' "6.016 type of contract or labor relation (from modelo 190) in `year'"
label val reltype190_`year' reltype190
label var geomob_`year' "6.018 geographic mobility in `year'"
label val geomob_`year' geomob
label var compens_`year' "6.021 compensatory pension (individual needs to pay the spouse) in `year'"
label var fann_`year' "6.022 food annuities to be paid to the spouse (due to children) in `year'"
label var child3_`year' "6.023 number of children under the age of 3 in `year'"
label var childr_`year' "6.025 number of children (above age 3) in `year'"
label var dischild33_`year' "6.027 number of disabled [33,65) children in `year'"
label var dischild33m_`year' "6.029 number of disabled [33,65) and with reduced mobility children in `year'"
label var dischild65_`year' "6.031 number of disabled [65,99] children in `year'"
label var child_`year' "6.033 total number of children in `year'"
label var ancestl75_`year' "6.034 number of ancestors <75y.o. in `year'"
label var ancestm75_`year' "6.036 number of ancestors >=75y.o. in `year'"
label var disanc33_`year' "6.038 ancestors with disability [33,65) in `year'"
label var disanc33m_`year' "6.040 ancestors with disability [33,65) and reduced mobility in `year'"
label var disanc65_`year' "6.042 ancestors with disability [65,99] in `year'"
label var ancest_`year' "6.044 total number of ancestors in `year'"

* store
sort id
compress
save "$data/mcvl`year'_06fisc.dta", replace
cap erase "$data/temp/mcvl`year'_06fisc.dta"
} 

* put together
use "$data/mcvl2017_06fisc.dta", clear
forval year = 2016(-1)2005 {
merge 1:1 id retkey using "$data/mcvl`year'_06fisc.dta", force
drop _merge
}

* store
sort id
compress
save "$data/mcvl2004-17_06fisc.dta", replace

* erase temp files
forval year = 2017(-1)2005 {
cap erase "$data/mcvl`year'_06fisc.dta"
}

}
********************************************************************************
* closing
********************************************************************************
{
cap log close
clear
}
